package course.dao.impl;

import course.dao.BaseDao;
import course.dao.StudentDao;
import course.pojo.entity.Student;
import course.pojo.vo.Page;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @author HLF
 * @date 2022/6/30 20:27
 */
public class StudentDaoImpl extends BaseDao<Student> implements StudentDao {
    @Override
    public Student getById(Integer sid, Connection connection) {
        String sql = "select * from t_student where sid = ?;";
        return getOneBean(connection, sql, sid);
    }

    @Override
    public int addStudent(Student student, Connection connection) {
        String sql = "insert into t_student(sid,sname,ssex,zid,spassword) values(?,?,?,?,?)";
        return update(connection, sql, student.getSid(), student.getSname(), student.getSsex(), student.getZid(), student.getSpassword());
    }

    @Override
    public int updateById(Student student, Connection connection) {
        String sql = "update t_student set sname = ?,ssex  = ?,zid = ? where sid = ?";
        return update(connection, sql, student.getSname(), student.getSsex(), student.getZid(), student.getSid());
    }

    @Override
    public int updateInfo(Student student, Connection connection) {
        String sql = "update t_student set ssex  = ?,spassword = ? where sid = ?";
        return update(connection, sql, student.getSsex(), student.getSpassword(), student.getSid());
    }

    @Override
    public int deleteById(Integer sid, Connection connection) {
        String sql = "delete from t_student where sid = ?;";
        return update(connection, sql, sid);
    }

    @Override
    public List<Student> getList(Connection connection) {
        String sql = "select * from t_student;";
        return getBeanList(connection, sql);
    }

    /**/

    @Override
    public int deleteSelect(List<Integer> ids, Connection connection) {
        String sql = "delete from t_student where sid in(?)";

        String sqlIN = ids.stream()
                .map(String::valueOf)
                .collect(Collectors.joining(",", "(", ")"));

        sql = sql.replace("(?)", sqlIN);

        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);

            return preparedStatement.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public long count(Connection connection) {
        String sql = "select count(*) from t_student;";
        return (long) getCount(connection, sql);
    }

    /**
     * 分页
     *
     * @param pageNo 当前页码数
     * @return 返回通用的分页对象
     */
    @Override
    public Page<Student> page(Integer pageNo, Connection connection) {
        Page<Student> page = new Page<>();
        Integer pageSize = page.getPageSize();

        String sql = "select * from t_student limit ?,?;";
        List<Student> list = getBeanList(connection, sql, (pageNo - 1) * pageSize, pageSize);

        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setList(list);
        long total = this.count(connection);
        page.setTotal(total);

        int pages = (int) Math.ceil((double) total / pageSize);
        page.setPages(pages);

        return page;
    }

    /**
     * 按照学生名称关键字查找，并返回分页对象
     */
    @Override
    public Page<Student> query(String sname, Integer pageNo, Connection connection) {
        Page<Student> page = new Page<>();
        Integer pageSize = page.getPageSize();

        String sql = "select * from t_student where sname like ? limit ?,?";
        String key = "%" + sname + "%";
        List<Student> list = getBeanList(connection, sql, key, (pageNo - 1) * pageSize, pageSize);

        String sql2 = "select count(*) from t_student where sname like ?;";
        long count = (long) getCount(connection, sql2, key);

        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setList(list);

        page.setTotal(count);

        int pages = (int) Math.ceil((double) count / pageSize);
        page.setPages(pages);

        return page;
    }

    @Override
    public List<Student> selectBathByIds(List<Integer> ids, Connection connection) {
        String sql = "select * from t_student where sid in(?)";

        String sqlIN = ids.stream()
                .map(x -> String.valueOf(x))
                .collect(Collectors.joining(",", "(", ")"));

        sql = sql.replace("(?)", sqlIN);

        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            ResultSet rs = preparedStatement.executeQuery();
            List<Student> students = new ArrayList<>();
            while (rs.next()) {
                Student student = new Student();
                student.setSid(rs.getInt(1));
                student.setSname(rs.getString(2));
                student.setSsex(rs.getString(3));
                student.setZid(rs.getInt(4));
                student.setSpassword(rs.getString(5));

                students.add(student);
            }
            return students;

        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}
